<?php 
require_once 'sesi_login.php';
require_once 'konfigurasi.php';
fungsi('umum');

define('PAGE', 'Laporan Polsek');
define('PAGE_ICO', 'icon-stats');
define('PAGE_URL', 'laporan.polsek.perkara.php');
	
koneksi_buka();
panggil_header();

$bulan = "";
$tahun = "";

if(isset($_POST['tahun'])) {
	$tahun = $_POST['tahun'];
	if($tahun == 0){
		$tahun = "";
	}
}
if(isset($_POST['bulan'])) {
	$bulan = $_POST['bulan'];
	if($bulan == 0){
		$bulan = "";
	}
}

$text_q_perkara = "select b.kd_kesatuan as 'kd_kesatuan', b.nm_kesatuan 'nm_kesatuan', a.ep as 'ep', a.epp as 'epp', a.euh as 'euh', a.jumlah as 'jumlah' 
					from (select * from r_kesatuan) b 
					left join 
						(select d.kd_kesatuan as 'kd_kesatuan', d.nm_kesatuan 'nm_kesatuan', a.ep as 'ep', b.epp as 'epp', c.euh as 'euh', d.jumlah as 'jumlah' 
							from (select a.kd_kesatuan as 'kd_kesatuan', a.nm_kesatuan as 'nm_kesatuan', count(b.kd_pidana) as 'jumlah' from r_kesatuan a 
								left join t_spdp b on b.kd_kesatuan = a.kd_kesatuan group by a.kd_kesatuan) d 
								left join (select a.kd_kesatuan as 'kd_kesatuan', count(b.kd_pidana) as 'ep' from r_kesatuan a 
								left join t_spdp b on b.kd_kesatuan = a.kd_kesatuan left join r_jenis_pidana c on b.kd_pidana = c.kd_pidana 
								where c.kd_pidana =1 group by a.kd_kesatuan) a on d.kd_kesatuan = a.kd_kesatuan 
							left join (select a.kd_kesatuan as 'kd_kesatuan', count(b.kd_pidana) as 'epp' 
										from r_kesatuan a left join t_spdp b on b.kd_kesatuan = a.kd_kesatuan 
											left join r_jenis_pidana c on b.kd_pidana = c.kd_pidana where c.kd_pidana =2 group by a.kd_kesatuan) b on d.kd_kesatuan = b.kd_kesatuan 
											left join (select a.kd_kesatuan as 'kd_kesatuan', count(b.kd_pidana) as 'euh' from r_kesatuan a left join t_spdp b on b.kd_kesatuan = a.kd_kesatuan 
											left join r_jenis_pidana c on b.kd_pidana = c.kd_pidana where c.kd_pidana =3 group by a.kd_kesatuan) c on d.kd_kesatuan = c.kd_kesatuan 
											left join t_spdp e on d.kd_kesatuan = e.kd_kesatuan left join t_perkara f on e.kd_perkara = f.kd_perkara 
											where MONTH(f.tgl_dimulai) like ('%$bulan%') AND YEAR(f.tgl_dimulai) like ('%$tahun%') group by 1) a on b.kd_kesatuan = a.kd_kesatuan group by b.kd_kesatuan";
					
$q_perkara = mysql_query($text_q_perkara);

?>

<div class="row-fluid">

	<div class="span12">
		<div class="da-panel collapsible">
			<div class="da-panel-header">
				<span class="da-panel-title">
					<i class="icon-notes-2"></i> Laporan
				</span>
			</div>

			<div class="da-panel-toolbar">
				<div class="btn-toolbar">
					<div class="btn-group">
						<a href="laporan.php" class="btn">Jumlah</a>
						<a href="laporan.jenis.perkara.php" class="btn">Jenis</a>
						<button class="btn disabled">Polsek</button>					
						<a href="laporan.beban.jaksa.php" class="btn">Beban Jaksa</a>
					</div>
				</div>
			</div>
			
			<div class="da-panel-content da-form-container">

				<form class="da-form da-form-inline" method="post" action="laporan.polsek.perkara.php">
					<div class="da-form-row da-column-row">
						<div class="da-form-item da-form-cols">
							<div class="col-2-8">
								<label class="da-form-label">Bulan</label>
								<select name="bulan" id="dc-target">
									<option value="0">Semua</option>
									<option value="1">Januari</option>
									<option value="2">Pebruari</option>
									<option value="3">Maret</option>
									<option value="4">April</option>
									<option value="5">Mei</option>
									<option value="6">Juni</option>
									<option value="7">Juli</option>
									<option value="8">Agustus</option>
									<option value="9">September</option>
									<option value="10">Oktober</option>
									<option value="11">Nopember</option>
									<option value="12">Desember</option>
								</select>
							</div>
							<div class="col-2-8">
								<label class="da-form-label">Tahun</label>
								<select name="tahun" id="dc-target">
									<option value="0">Semua</option>
									<?php	
										$q_thn_perkara = mysql_query("select YEAR(tgl_dimulai) as y_perkara from t_perkara group by YEAR(tgl_dimulai)");
										while($dt_thn_perkara = mysql_fetch_array($q_thn_perkara)){
									?>
											<option value="<?php echo $dt_thn_perkara['y_perkara']?>"><?php echo $dt_thn_perkara['y_perkara']?></option>
									<?php } ?>
								</select>
							</div>
							<div class="col-2-8">
								<input type="submit" value="Tampilkan" class="btn btn-danger" />
								<input type="hidden" id="bulan" value="<?php echo $bulan==''?'-':$bulan;?>" />
								<input type="hidden" id="tahun" value="<?php echo $tahun==''?'-':$tahun;?>" />
	
							</div>
						</div>
					</div>
				</form>
			</div>
			<div class="da-panel-content da-table-container">
				<table id="da-ex-datatable-numberpaging" class="da-table">
					<thead>
						<tr>
							<th>No</th>
							<th>Polsek</th>
							<th>Jumlah Ep1</th>
							<th>Jumlah Epp1</th>
							<th>Jumlah Euh</th>
							<th>Jumlah Total</th>
						</tr>
					</thead>
					<tbody>
						<?php
							$i=1;
							while($dt_perkara=mysql_fetch_array($q_perkara)){?>
							<tr>
								<td><?php echo $i; $i++;?></td>
								<td><?php echo $dt_perkara['nm_kesatuan'];?></td>
								<td><?php echo $dt_perkara['ep']!=null?$dt_perkara['ep']:0;?></td>
								<td><?php echo $dt_perkara['epp']!=null?$dt_perkara['epp']:0;?></td>
								<td><?php echo $dt_perkara['euh']!=null?$dt_perkara['euh']:0;?></td>
								<td><?php echo $dt_perkara['jumlah']!=null?$dt_perkara['jumlah']:0;?></td>
							</tr>
						<?php };?>
						
					</tbody>
				</table>
			</div>
			<!--
			<div class="da-panel-content">
				<h3>Statistik Jumlah Perkara Tahun 2013</h3>
				<div id="chart-polsek-perkara" style="height:300px;"></div>
			</div>
			-->
		</div>
	</div>

</div>

<?php
panggil_footer();
koneksi_tutup();
?>
